package nftrainer.dbconnect;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import language.Languages;

import nftrainer.dbconnect.interfaces.IDBConnector;
import nftrainer.helper.Parser;
import nftrainer.taskmanager.Task;


public class DBConnector implements IDBConnector{
	
	private Connection con;
	private PreparedStatement prep;
	private ResultSet res;
	private ResultSetMetaData rsmd;
	
	private DataSource daso = null;
	
	private String GETTASKAMOUNT = "SELECT COUNT(id) FROM aufgaben";
	private String GETRANDOMTASK = "SELECT a.aufgabentext, a.nf, l.unteraufgabenid, l.loesung FROM aufgaben a INNER JOIN loesungen l ON (a.id = l.aufgabenid) WHERE a.id = ? ORDER BY l.unteraufgabenid";
	
	private String GETTASKAMOUNT_EN = "SELECT COUNT(id) FROM aufgaben_en";
	private String GETRANDOMTASK_EN = "SELECT a.aufgabentext, a.nf, l.unteraufgabenid, l.loesung FROM aufgaben_en a INNER JOIN loesungen_en l ON (a.id = l.aufgabenid) WHERE a.id = ? ORDER BY l.unteraufgabenid";
	
	public DBConnector () {
	}
	
	private void getDataSource()  {
        try  {
        	Context cxt = new InitialContext();
        	Context envContext  =  (Context) cxt.lookup("java:/comp/env");       	
        	daso = (DataSource)envContext.lookup("jdbc/fhportal");
        } catch(NamingException namEx) {
            System.err.println("Error: " + namEx.getMessage());
        }
    }
	
	@Override
	public void openConnection() {
		if (this.daso == null) {
			this.getDataSource();
		}
		try {
			this.con = this.daso.getConnection();
		} catch (SQLException e) {
		}
	}
	
	@Override
	public Connection getConnection() throws SQLException {
		if (this.daso == null) {
			this.getDataSource();
		}
		return this.daso.getConnection();
	}

	@Override
	public void getRandomTask(Task task, Languages lang) {
		Parser parser = new Parser();
		try {
			switch (lang) {
			case de_DE:
				prep = con.prepareStatement(GETRANDOMTASK, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				break;
			case en_GB:
				prep = con.prepareStatement(GETRANDOMTASK_EN, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				break;
			default:
				prep = con.prepareStatement(GETRANDOMTASK, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
				break;
			}
			
			prep.setInt(1, task.getID());
			res = prep.executeQuery();
			if (res.next()) {
				task.setText(res.getString(1));
				task.setNF(res.getInt(2));
			}
			res.beforeFirst();
			while (res.next()) {
				switch (res.getInt(3)) {
				case 1:
					break;
				case 2:
					task.addNotFirstNF(res.getString(4));
					break;
				case 3:
					task.addToFunctional(parser.parse(res.getString(4)));
					break;
				case 4:
					task.addPrimary(res.getString(4));
					break;
				case 5:
					task.addToSecondNF(parser.parse(res.getString(4)));
					break;
				case 6:
					task.addToThirdNF(parser.parse(res.getString(4)));
					break;
				default:
					break;
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public int getTaskAmount(Languages lang) {
		try {
			switch (lang) {
			case de_DE:
				prep = con.prepareStatement(GETTASKAMOUNT);
				break;
			case en_GB:
				prep = con.prepareStatement(GETTASKAMOUNT_EN);
				break;
			default:
				prep = con.prepareStatement(GETTASKAMOUNT);
				break;
			}
			
			res = prep.executeQuery();
			if (res.next()) {
				return res.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				prep.close();
				res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	@Override
	public String[][] loadTaskTable(String sql) {
		try {
			prep = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
			res = prep.executeQuery();
			rsmd = res.getMetaData();
			
			int columnCount = rsmd.getColumnCount();
			
			res.last();
			String[][] result = new String[res.getRow()+1][columnCount];
			res.beforeFirst();
			
			for (int i = 0; i < columnCount; i++) {
				result[0][i] = rsmd.getColumnName(i+1); 
			}
			
			int rowid = 1;
			while (res.next()) {
				for (int i = 0; i < columnCount; i++) {
					result[rowid][i] = res.getString(i+1); 
				}
				rowid++;
			}
			prep.close();
			res.close();
			return result;
		} catch (SQLException e) {e.printStackTrace();}
		return null;
	}
	
}
